﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Bitirme.DAL.Entities;
using System.Data.SqlClient;
using Bitirme.DAL.Utils;
using System.Data;
using Bitirme.DAL.Enum;

namespace Bitirme.DAL.Data_Access
{
    public class EmployeeToCapabilityCollection
    {
        public static int InsertEmployeeToCapability(int employeeID, Dictionary<int, int> capabilityMap)
        {

            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parEmployeeID = null;
            SqlParameter parCapabilityID = null;
            SqlParameter parSkill = null;


            int insertedEmployeeID = -1;
            foreach (KeyValuePair<int, int> caps in capabilityMap)
            {
                try
                {
                    myConnection = SQLUtils.GetConnection();
                    cmd = new SqlCommand("InsertEmployeeToCapability", myConnection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = myConnection; // ??


                    parEmployeeID = new SqlParameter();
                    parEmployeeID.ParameterName = "@employeeID";
                    parEmployeeID.Value = employeeID;
                    parEmployeeID.SqlDbType = SqlDbType.Int;

               
                        parCapabilityID = new SqlParameter();
                        parCapabilityID.ParameterName = "@capabilityID";
                        parCapabilityID.Value = caps.Key;
                        parCapabilityID.SqlDbType = SqlDbType.Int;

                        parSkill = new SqlParameter();
                        parSkill.ParameterName = "@skill";
                        parSkill.Value = caps.Value;
                        parSkill.SqlDbType = SqlDbType.Int;



                        cmd.Parameters.Add(parEmployeeID);
                        cmd.Parameters.Add(parCapabilityID);
                        cmd.Parameters.Add(parSkill);

                        insertedEmployeeID = employeeID;
                        myConnection.Open();
                        cmd.ExecuteScalar();
                
                }
                catch (SqlException e)
                {

                    insertedEmployeeID = -1;
                    Console.WriteLine(e);
                }
            }

            if (myConnection != null)
            {

                myConnection.Close();
                myConnection.Dispose();
            }
        


            return insertedEmployeeID;
        }

        public static Dictionary<Capability, SkillLevel> GetCapabilitiesByEmployeeId(int employeeID)
        {
            Dictionary<Capability, SkillLevel> skills = new Dictionary<Capability, SkillLevel>();
            SqlConnection myConnection = null;
            SqlDataReader myReader = null;
            SqlCommand cmd = null;
            SqlParameter parEmployeeID = null;

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("GetCapabilitiesByEmployeeId", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                parEmployeeID = new SqlParameter();
                parEmployeeID.ParameterName = "@employeeID";
                parEmployeeID.Value = employeeID;
                parEmployeeID.SqlDbType = SqlDbType.Int;


                cmd.Parameters.Add(parEmployeeID);

                myConnection.Open();
                myReader = cmd.ExecuteReader();
                while (myReader.Read())
                {
                    Capability tempCapability = new Capability();
                    SkillLevel tempSkillLevel;

                    int ID;
                    Int32.TryParse(SQLUtils.NVL(myReader["capabilityID"], 0).ToString(), out ID);
                    tempCapability.ID = ID;
                    tempCapability.Name = SQLUtils.NVL(myReader["capabilityNAME"], String.Empty).ToString();
                    int skill;
                    Int32.TryParse(SQLUtils.NVL(myReader["capabilitySKILL"], 0).ToString(), out skill);
                    switch (skill)
                    {
                        case 0:
                            tempSkillLevel = SkillLevel.None;
                            break;
                        case 1:
                            tempSkillLevel = SkillLevel.Low;
                            break;
                        case 2:
                            tempSkillLevel = SkillLevel.Medium;
                            break;
                        case 3:
                            tempSkillLevel = SkillLevel.High;
                            break;
                        default:
                            tempSkillLevel = SkillLevel.None;
                            break;
                    }

                    skills.Add(tempCapability, tempSkillLevel);
                }
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
            }

            finally
            {
                if (myReader != null)
                {
                    myReader.Close();
                    myReader.Dispose();
                }

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }



            return skills;
        }

        public static Boolean DeleteEmployeeToCapabilityByEmployeeID(int employeeID)
        {

            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parEmployeeID = null;
            bool isDeleteSuccessful = false;

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("DeleteEmployeeToCapabilityByEmployeeID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                parEmployeeID = new SqlParameter();
                parEmployeeID.ParameterName = "@employeeID";
                parEmployeeID.Value = employeeID;
                parEmployeeID.SqlDbType = SqlDbType.Int;


                cmd.Parameters.Add(parEmployeeID);


                myConnection.Open();

                cmd.ExecuteScalar();
                isDeleteSuccessful = true;
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
                isDeleteSuccessful = false;
            }

            finally
            {

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return isDeleteSuccessful;
        
        
        }
    }
}